package com.work.demo.test;

import cn.hutool.core.date.DateUtil;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DatabasePaginationToFile {

    public static void main(String[] args) {
        try {
            String time = DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss");
            paginateAndWrite("apc_oss_config","apc_oss_config" + time +".csv");
            paginateAndWrite("apc_whitelist","apc_whitelist" + time +".csv");


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void paginateAndWrite(String tableName, String filename) throws SQLException, IOException {
        int pageSize = 100;
        int pageNum = 1;
        boolean isFirstPage = true;

        try (Connection conn = DatabaseConfig.getConnection();
             BufferedWriter writer = new BufferedWriter(new FileWriter(filename))) {

            while (true) {
                String query = String.format("SELECT * FROM %s LIMIT ? OFFSET ?", tableName);
                try (PreparedStatement stmt = conn.prepareStatement(query)) {
                    stmt.setInt(1, pageSize);
                    stmt.setInt(2, (pageNum - 1) * pageSize);
                    ResultSet rs = stmt.executeQuery();

                    if (!rs.next()) {
                        break;
                    }
                    if (isFirstPage) {
                    List<String> columns = getColumnNames(rs);
                    writer.write(String.join(",", columns));
                    writer.newLine();
                    isFirstPage = false;
                    }

                    while (rs.next()) {
                        writer.write(getRowData(rs, getColumnNames(rs)));
                        writer.newLine();
                    }
                    pageNum++;
                }
            }
        }
    }

    private static List<String> getColumnNames(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        List<String> columns = new ArrayList<>();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            columns.add(metaData.getColumnName(i));
        }
        return columns;
    }

    private static String getRowData(ResultSet rs, List<String> columns) throws SQLException {
        StringBuilder row = new StringBuilder();
        for (String column : columns) {
            row.append(rs.getString(column)).append(",");
        }
        return row.substring(0, row.length() - 1);
    }
}
